package com.xishu.dao.jpa;

import com.xishu.entity.order.OrderItemMapping;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import javax.persistence.Tuple;
import java.util.List;

public interface OrderItemMappingDao extends JpaRepository<OrderItemMapping, Long>, JpaSpecificationExecutor<OrderItemMapping> {

    /**
     * 总计选项，带菜品中文名称查询金额总计
     * @param shopIdList
     * @param startTime
     * @param endTime
     * @param timeUnit
     * @param itemName_zh
     * @return
     */
    @Query(value = "select  FROM_UNIXTIME(create_time / 1000, :timeUnit) as x , item_name as y, item_name_zh as y_zh, item_name_en as y_en , item_id as tmpId,catalog_id as catalogId, sum(cash) as value " +
            "from order_item_mapping m " +
            "where m.item_count > 0 and  m.shop_id in (:shopIdList) and m.create_time >= :startTime and m.create_time <= :endTime and item_name_zh in (:itemName_zh)  " +
            " group by m.item_name_zh,FROM_UNIXTIME(create_time / 1000, :timeUnit)" +
            "order by sum(cash) desc", nativeQuery = true)
    public List<Tuple> listIdToReportAmountZh(List<Long> shopIdList, Long startTime, Long endTime,String timeUnit, List<String> itemName_zh);

    /**
     * 总计选项，带菜品英文名称查询金额总计
     * @param shopIdList
     * @param startTime
     * @param endTime
     * @param timeUnit
     * @param itemName_en
     * @return
     */
    @Query(value = "select  FROM_UNIXTIME(create_time / 1000, :timeUnit) as x , item_name as y, item_name_zh as y_zh, item_name_en as y_en , item_id as tmpId,catalog_id as catalogId, sum(cash) as value " +
            "from order_item_mapping m " +
            "where m.item_count > 0 and  m.shop_id in (:shopIdList) and m.create_time >= :startTime and m.create_time <= :endTime and item_name_en in (:itemName_en)  " +
            " group by m.item_name_zh,FROM_UNIXTIME(create_time / 1000, :timeUnit)" +
            "order by sum(cash) desc", nativeQuery = true)
    public List<Tuple> listIdToReportAmountEn(List<Long> shopIdList, Long startTime, Long endTime,String timeUnit, List<String> itemName_en);

    /**
     * 总计选项，不带菜品名称查询金额总计
     * @param shopIdList
     * @param startTime
     * @param endTime
     * @param timeUnit
     * @return
     */
    @Query(value = "select  FROM_UNIXTIME(create_time / 1000, :timeUnit) as x , item_name as y, item_name_zh as y_zh, item_name_en as y_en , item_id as tmpId,catalog_id as catalogId, sum(cash) as value " +
            "from order_item_mapping m " +
            "where m.item_count > 0 and  m.shop_id in (:shopIdList) and m.create_time >= :startTime and m.create_time <= :endTime " +
            " group by m.item_name,FROM_UNIXTIME(create_time / 1000, :timeUnit)" +
            "order by sum(cash) desc", nativeQuery = true)
    public List<Tuple> reportAmount(List<Long> shopIdList, Long startTime, Long endTime,String timeUnit);


    /**
     * 年月日选项，带菜品中文名称集合查询数量总计
     * @param shopIdList
     * @param startTime
     * @param endTime
     * @param timeUnit
     * @param itemName_zh
     * @return
     */
    @Query(value = "select  FROM_UNIXTIME(create_time / 1000, :timeUnit) as x , item_name as y, item_name_zh as y_zh, item_name_en as y_en , item_id as tmpId,catalog_id as catalogId, sum(item_count) as value " +
            "from order_item_mapping m " +
            "where m.item_count > 0 and  m.shop_id in (:shopIdList) and m.create_time >= :startTime and m.create_time <= :endTime and item_name_zh in (:itemName_zh)  " +
            
            " group by m.item_name_zh,FROM_UNIXTIME(create_time / 1000, :timeUnit)" +
            "order by sum(item_count) desc", nativeQuery = true)
    List<Tuple> listIdToReportQtyZh(List<Long> shopIdList, Long startTime, Long endTime,String timeUnit, List<String> itemName_zh);

    /**
     * 年月日选项，带菜品英文名称集合查询数量总计
     * @param shopIdList
     * @param startTime
     * @param endTime
     * @param timeUnit
     * @param itemName_en
     * @return
     */
    @Query(value = "select  FROM_UNIXTIME(create_time / 1000, :timeUnit) as x , item_name as y, item_name_zh as y_zh, item_name_en as y_en , item_id as tmpId,catalog_id as catalogId, sum(item_count) as value " +
            "from order_item_mapping m " +
            "where m.item_count > 0 and  m.shop_id in (:shopIdList) and m.create_time >= :startTime and m.create_time <= :endTime and item_name_zh en (:itemName_en)  " +
            " and item_count > 0 " +
            " group by m.item_name_zh,FROM_UNIXTIME(create_time / 1000, :timeUnit)" +
            "order by sum(item_count) desc", nativeQuery = true)
    List<Tuple> listIdToReportQtyEn(List<Long> shopIdList, Long startTime, Long endTime,String timeUnit, List<String> itemName_en);


    /**
     * 年月日选项，不带菜品名称集合查询数量总计
     * @param shopIdList
     * @param startTime
     * @param endTime
     * @param timeUnit
     * @return
     */
    @Query(value = "select  FROM_UNIXTIME(create_time / 1000, :timeUnit) as x , item_name as y, item_name_zh as y_zh, item_name_en as y_en , item_id as tmpId,catalog_id as catalogId, sum(item_count) as value " +
            "from order_item_mapping m " +
            "where m.item_count > 0 and  m.shop_id in (:shopIdList) and m.create_time >= :startTime and m.create_time <= :endTime  " +
            " group by m.item_id,FROM_UNIXTIME(create_time / 1000, :timeUnit)" +
            "order by sum(item_count) desc", nativeQuery = true)
    List<Tuple> reportQty(List<Long> shopIdList, Long startTime, Long endTime,String timeUnit);


    /**
     * 总计选项，带菜品中文名称查询金额总计
     * @param shopIdList
     * @param startTime
     * @param endTime
     * @param itemName_zh
     * @return
     */
    @Query("select itemId as tmpId,sum(cash) as value, itemName as y, itemName_zh as y_zh, itemName_en as y_en  from order_item_mapping m " +
            "where m.itemCount > 0 and   m.createTime >= :startTime and m.createTime <= :endTime " +
            "and m.itemName_zh in (:itemName_zh)" +
            "and m.shopId in (:shopIdList) " +
            " group by m.itemName_zh " +
            "order by sum(cash) desc")
    public List<Tuple> idTotalAmountZh(List<Long> shopIdList, Long startTime, Long endTime, List<String> itemName_zh);

    /**
     * 总计选项，带菜品英文名称查询金额总计
     * @param shopIdList
     * @param startTime
     * @param endTime
     * @param itemName_en
     * @return
     */
    @Query("select itemId as tmpId,sum(cash) as value, itemName as y, itemName_zh as y_zh, itemName_en as y_en  from order_item_mapping m " +
            "where m.itemCount > 0 and   m.createTime >= :startTime and m.createTime <= :endTime " +
            "and m.itemName_en in (:itemName_en)" +
            "and m.shopId in (:shopIdList)" +
            " group by m.itemName_en " +
            "order by sum(cash) desc")
    public List<Tuple> idTotalAmountEn(List<Long> shopIdList, Long startTime, Long endTime, List<String> itemName_en);

    /**
     * 总计选项，不带菜品名称查询金额总计
     * @param shopIdList
     * @param startTime
     * @param endTime
     * @return
     */
    @Query("select itemId as tmpId,sum(cash) as value, itemName as y, itemName_zh as y_zh, itemName_en as y_en  from order_item_mapping m " +
            "where m.itemCount > 0 and  m.createTime >= :startTime and m.createTime <= :endTime " +
            "and m.shopId in (:shopIdList)" +
            " group by m.itemName " +
            "order by sum(cash) desc")
    public List<Tuple> totalAmount(List<Long> shopIdList, Long startTime, Long endTime);


    /**
     * 总计选项，带菜品中文名称查询数量总计
     * @param shopIdList
     * @param startTime
     * @param endTime
     * @param itemName_zh
     * @return
     */
    @Query("select itemId as tmpId,sum(itemCount) as value, itemName as y, itemName_zh as y_zh, itemName_en as y_en  from order_item_mapping m " +
            "where m.itemCount > 0 and  m.shopId in (:shopIdList) and m.createTime >= :startTime and m.createTime <= :endTime and item_name_zh in (:itemName_zh)" +
            " group by m.itemName_zh " +
            "order by sum(itemCount) desc")
    List<Tuple> idTotalQtyZh(List<Long> shopIdList, Long startTime, Long endTime, List<String> itemName_zh);


    /**
     * 总计选项，带菜品英文名称查询数量总计
     * @param shopIdList
     * @param startTime
     * @param endTime
     * @param itemName_en
     * @return
     */
    @Query("select itemId as tmpId,sum(itemCount) as value, itemName as y, itemName_zh as y_zh, itemName_en as y_en  from order_item_mapping m " +
            "where m.itemCount > 0 and  m.shopId in (:shopIdList) and m.createTime >= :startTime and m.createTime <= :endTime and item_name_en in (:itemName_en)" +
            " group by m.itemName_en " +
            "order by sum(itemCount) desc")
    List<Tuple> idTotalQtyEn(List<Long> shopIdList, Long startTime, Long endTime, List<String> itemName_en);


    /**
     * 总计选项，不带菜品名称查询数量总计
     * @param shopIdList
     * @param startTime
     * @param endTime
     * @return
     */
    @Query("select itemId as tmpId,sum(itemCount) as value, itemName as y, itemName_zh as y_zh, itemName_en as y_en  from order_item_mapping m " +
            "where m.itemCount > 0 and  m.createTime >= :startTime and m.createTime <= :endTime " +
            "and m.shopId in (:shopIdList)" +
            " group by m.itemName " +
            "order by sum(itemCount) desc")
    public List<Tuple> totalQty(List<Long> shopIdList, Long startTime, Long endTime);


    @Query(value = "select item_name_zh as name_zh,item_name_en as name_en from order_item_mapping m " +
            "where m.item_count > 0 and  m.shop_id =:shopId   group by item_name_zh, item_name_en", nativeQuery = true)
    public List<Tuple> shopSearchItem(Long shopId);
}
